﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using BrightSword.OfficeInterop.Internal;
using BrightSword.SwissKnife;
using Microsoft.Office.Interop.Excel;

namespace BrightSword.OfficeInterop
{
    public static class ExcelObjectReader
    {
        public static IEnumerable<ContextfulMappedObject<TTemplate>> ReadItems<TTemplate>(string filename, string sheetName = null, int headerRowIndex = 1)
            where TTemplate : new()
        {
            var templateMapper = new TemplateMapper<TTemplate>();

            using (var reader = new ExcelReadProcessor(filename))
            {
                var worksheets = reader.Workbook.Sheets;

                var sheets = String.IsNullOrEmpty(sheetName)
                    ? worksheets.Cast<Worksheet>()
                    : worksheets.Cast<Worksheet>().Where(_sheet => String.Compare(_sheet.Name, sheetName, true) == 0);

                try
                {
                    foreach (var worksheet in sheets)
                    {
                        try
                        {
                            var rows = worksheet.Rows;

                            var maxColumnSeen = templateMapper.SetupHeaders(worksheet, headerRowIndex);

                            try
                            {
                                for (var rowIndex = 1; rowIndex < rows.Rows.Count; rowIndex++)
                                {
                                    if (rowIndex == headerRowIndex)
                                    {
                                        continue;
                                    }

                                    if (worksheet.IsBlank(rowIndex, maxColumnSeen))
                                    {
                                        break;
                                    }

                                    yield return new ContextfulMappedObject<TTemplate>
                                                 {
                                                     WorksheetName = worksheet.Name,
                                                     RowIndex = rowIndex,
                                                     MappedObject = templateMapper.Map(worksheet, rowIndex)
                                                 };
                                }
                            }
                            finally
                            {
                                Marshal.ReleaseComObject(rows);
                            }
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(worksheet);
                        }
                    }

                    yield break;
                }
                finally
                {
                    Marshal.ReleaseComObject(worksheets);
                }
            }
        }

        #region Nested type: TemplateMapper

        internal class TemplateMapper<TTemplate>
            where TTemplate : new()
        {
            private IDictionary<string, Tuple<int, PropertyInfo>> _columnPropertyMap;

            public int SetupHeaders(Worksheet worksheet, int headerRowIndex = 1)
            {
                _columnPropertyMap = new Dictionary<string, Tuple<int, PropertyInfo>>();

                var rows = worksheet.Rows;
                try
                {
                    var headerRow = rows[headerRowIndex];
                    try
                    {
                        var columns = rows.Columns;
                        try
                        {
                            var attributedProperties = from _pi in typeof (TTemplate).GetProperties()
                                                       let __attr = _pi.GetCustomAttribute<ColumnHeaderAttribute>()
                                                       select new {
                                                           ColumnHeader = __attr.ColumnHeaderText,
                                                           PropertyInfo = _pi
                                                       };
                            var maxColumnSeen = 0;

                            foreach (var attributedProperty in attributedProperties)
                            {
                                for (var columnIndex = 1; columnIndex < columns.Count; columnIndex++)
                                {
                                    var cell = rows.Cells[headerRowIndex, columnIndex] as Range;
                                    if (cell == null)
                                    {
                                        continue;
                                    }
                                    try
                                    {
                                        var headerText = cell.Text as string;
                                        if (String.IsNullOrWhiteSpace(headerText))
                                        {
                                            continue;
                                        }

                                        if (String.Compare(headerText, attributedProperty.ColumnHeader, true) != 0)
                                        {
                                            continue;
                                        }

                                        maxColumnSeen = Math.Max(maxColumnSeen, columnIndex);

                                        _columnPropertyMap[attributedProperty.ColumnHeader] = new Tuple<int, PropertyInfo>(
                                            columnIndex, attributedProperty.PropertyInfo);
                                        break;

                                    }
                                    finally
                                    {
                                        Marshal.ReleaseComObject(cell);
                                    }
                                }
                            }

                            return maxColumnSeen;
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(columns);
                        }
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(headerRow);
                    }
                }
                finally
                {
                    Marshal.ReleaseComObject(rows);
                }
            }

            internal TTemplate Map(Worksheet worksheet, int rowIndex)
            {
                var result = new TTemplate();

                var rows = worksheet.Rows;
                try
                {
                    var headerRow = rows[rowIndex];
                    try
                    {
                        var columns = rows.Columns;
                        try
                        {
                            foreach(var kvp in _columnPropertyMap)
                            {
                                var columnIndex = kvp.Value.Item1;
                                var propertyInfo = kvp.Value.Item2;

                                var cell = rows.Cells[rowIndex, columnIndex] as Range;
                                if (cell == null)
                                {
                                    continue;
                                }

                                try
                                {
                                    var value = cell.Text as string;
                                    if (!(String.IsNullOrEmpty(value)))
                                    {
                                        value = value.Trim();
                                    }

                                    var typedValue = value.CoerceType(propertyInfo.PropertyType, null);
                                    propertyInfo.SetValue(result, typedValue, null);
                                }
                                finally
                                {
                                    Marshal.ReleaseComObject(cell);
                                }
                            }
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(columns);
                        }
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(headerRow);
                    }
                }
                finally
                {
                    Marshal.ReleaseComObject(rows);
                }

                return result;
            }
        }

        #endregion
    }
}